package org.dong.samples.dao.Impl;

import java.sql.Timestamp;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.dong.samples.dao.ScoreHibernateDAO;
import org.dong.samples.model.Score;
import org.dong.samples.model.Subject;
import org.dong.samples.model.vo.ScoreVO;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.hibernate.type.DoubleType;
import org.hibernate.type.StringType;
import org.hibernate.type.TimestampType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class ScorehibernateDAOImpl extends BaseHibernateDAO<Score> implements ScoreHibernateDAO  {
	@Autowired
	private SessionFactory sessionFactory;
	
	public Session getSession(){
		return this.sessionFactory.getCurrentSession();
	}

	@Override
	public ScoreVO getScoreById(Integer scoreId) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select user.userName as userName, sub.subName as subjectName, es.examTime as examTime, es.score as score ");
		hql.append(" from Score es, User user, Subject sub ");
		hql.append(" where es.userId=user.userId ");
		hql.append(" and es.subjectId=sub.subId ");
		hql.append(" and es.id = :scoreId ");
		return (ScoreVO) this.createQuery(hql.toString())
				.setResultTransformer(Transformers.aliasToBean(ScoreVO.class))
				.setInteger("scoreId", scoreId)
				.uniqueResult();
	}

	@Override
	public Score getScore(String userId, Integer subId) {
		String hql = " FROM Score es WHERE es.userId = :userId AND es.subjectId = :subjectId ";
		return (Score) this.createQuery(hql)
				.setString("userId", userId)
				.setInteger("subjectId", subId)
				.uniqueResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScoreVO> getAllScore(Map<String, String> conditionMap) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select es.id as id, user.userId as userId, user.userName as userName, sub.subId as subjectId, sub.name as subjectName ");
		hql.append(" from Score es, User user, Subject sub ");
		hql.append(" where es.userId = user.userId ");
		hql.append(" and es.subjectId = sub.subId ");
		
		if(conditionMap !=null){
			Iterator<Entry<String, String>> it = conditionMap.entrySet().iterator();
			while (it.hasNext()) {
				Entry<String, String> currentCondition = it.next();
				if("subjectName".equals(currentCondition.getKey())){
					hql.append(" and sub.name like '%'" + currentCondition.getValue() + "'%' ");
				}else if("userId".equals(currentCondition.getKey())){
					hql.append(" and user.userId like '%'" + currentCondition.getValue() + "'%' ");
				}else if("userName".equals(currentCondition.getKey())){
					hql.append(" and user.userName like '%'" + currentCondition.getValue() + "'%' ");
				}else if("exameTime".equals(currentCondition.getKey())){
					if(currentCondition.getValue()!=null){
						hql.append(" and DATE_FORMAT(es.examTime, '%Y-%M-%d %H:%i') like '%'" + currentCondition.getValue() + "'%' ");
					}
				}
			}
		}
		return this.createQuery(hql.toString())
				.setResultTransformer(Transformers.aliasToBean(ScoreVO.class))
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Subject> getAllSubjectByUserId(String userId) {
		String sql = " SELECT es.sub_id as subId, "
				+ "es.`name` as subName,"
				+ "es.description as description,"
				+ "es.test_count as testCount,"
				+ "es.total_score as totalScore,"
				+ "es.total_time as totalTime,"
				+ "es.`status` as status"
				+ " FROM exam_user_subject eus,exam_subject es "
				+ "WHERE eus.sub_id=es.sub_id AND eus.user_id="+userId;
		List<Subject> s = this.createSqlQuery(sql).list();
		return s;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScoreVO> getScoreBySubject(String subName) {
		StringBuilder hql = new StringBuilder();
		hql.append(" SELECT eu.user_id as userId, eu.user_name as userName, es.`name` as subName, es.description, s.exam_time as examTime, s.score as score ");
		hql.append(" FROM exam_score s, exam_subject es, exam_user eu ");
		hql.append(" where s.user_id = eu.user_id ");
		hql.append(" and s.subject_id = es.sub_id ");
		hql.append(" and es.name = :subName ");
		List<ScoreVO> vo = this.createSqlQuery(hql.toString())
				.addScalar("userId", StringType.INSTANCE)
				.addScalar("userName", StringType.INSTANCE)
				.addScalar("subName", StringType.INSTANCE)
				.addScalar("description", StringType.INSTANCE)
				.addScalar("examTime", TimestampType.INSTANCE)
				.addScalar("score", DoubleType.INSTANCE)
				.setResultTransformer(Transformers.aliasToBean(ScoreVO.class))
				.setString("subName", subName)
				.list();
		return vo;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScoreVO> getScoreByuserId(String userId) {
		StringBuilder hql = new StringBuilder();
		hql.append(" SELECT eu.user_id as userId, eu.user_name as userName, es.`name` as subName, es.description, s.exam_time as examTime, s.score as score ");
		hql.append(" FROM exam_score s, exam_subject es, exam_user eu ");
		hql.append(" where s.user_id = eu.user_id ");
		hql.append(" and s.subject_id = es.sub_id ");
		hql.append(" and eu.user_id = :userId ");
		List<ScoreVO> vo = this.createSqlQuery(hql.toString())
				.addScalar("userId", StringType.INSTANCE)
				.addScalar("userName", StringType.INSTANCE)
				.addScalar("subName", StringType.INSTANCE)
				.addScalar("description", StringType.INSTANCE)
				.addScalar("examTime", TimestampType.INSTANCE)
				.addScalar("score", DoubleType.INSTANCE)
				.setResultTransformer(Transformers.aliasToBean(ScoreVO.class))
				.setString("userId", userId)
				.list();
		return vo;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScoreVO> getScoreByuserName(String userName) {
		StringBuilder hql = new StringBuilder();
		hql.append(" SELECT eu.user_id as userId, eu.user_name as userName, es.`name` as subName, es.description, s.exam_time as examTime, s.score as score ");
		hql.append(" FROM exam_score s, exam_subject es, exam_user eu ");
		hql.append(" where s.user_id = eu.user_id ");
		hql.append(" and s.subject_id = es.sub_id ");
		hql.append(" and eu.user_name = :userName ");
		List<ScoreVO> vo = this.createSqlQuery(hql.toString())
				.addScalar("userId", StringType.INSTANCE)
				.addScalar("userName", StringType.INSTANCE)
				.addScalar("subName", StringType.INSTANCE)
				.addScalar("description", StringType.INSTANCE)
				.addScalar("examTime", TimestampType.INSTANCE)
				.addScalar("score", DoubleType.INSTANCE)
				.setResultTransformer(Transformers.aliasToBean(ScoreVO.class))
				.setString("userName", userName)
				.list();
		return vo;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScoreVO> scoreHibernateDAO(String timestamp, String timestamp2) {
		StringBuilder hql = new StringBuilder();
		hql.append(" SELECT eu.user_id as userId, eu.user_name as userName, es.`name` as subName, es.description, s.exam_time as examTime, s.score as score ");
		hql.append(" FROM exam_score s, exam_subject es, exam_user eu ");
		hql.append(" where s.user_id = eu.user_id ");
		hql.append(" and s.subject_id = es.sub_id ");
		hql.append(" and s.exam_time > :timestamp ");
		hql.append(" and s.exam_time < :timestamp2 ");
		List<ScoreVO> vo = this.createSqlQuery(hql.toString())
				.addScalar("userId", StringType.INSTANCE)
				.addScalar("userName", StringType.INSTANCE)
				.addScalar("subName", StringType.INSTANCE)
				.addScalar("description", StringType.INSTANCE)
				.addScalar("examTime", TimestampType.INSTANCE)
				.addScalar("score", DoubleType.INSTANCE)
				.setResultTransformer(Transformers.aliasToBean(ScoreVO.class))
				.setString("timestamp", timestamp)
				.setString("timestamp2", timestamp2)
				.list();
		return vo;
	}

	@Override
	public void saveScore(Score score) {
		String userId = score.getUserId();
		Integer subId = score.getSubjectId();
		Timestamp time = score.getExamTime();
		Integer cj = (int) score.getScore();
		String sql = " INSERT INTO exam_score(user_id,subject_id, exam_time, score) VALUES(:userId, :subId, :time, :score) ";
		this.createSqlQuery(sql)
		.setString("userId", userId)
		.setInteger("subId", subId)
		.setTimestamp("time", time)
		.setInteger("score", cj)
		.executeUpdate();
	}

	@Override
	public void saveChengji(int score) {
		
	}
	
	
}
